/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;


/**
 *
 * @author Felix
 */
public class BaseDAO {
    static{//只會執行一次
        try {//這裡需加try catch，jsp的java區塊則是自動加入
            Class.forName("com.mysql.jdbc.Driver");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        
    }
    
    public Connection getConnection() throws Exception{
        Context initCtx = new InitialContext();
        Context envCtx = (Context) initCtx.lookup("java:comp/env");
        DataSource ds = (DataSource)
        envCtx.lookup("jdbc/javaDB");

        Connection conn = ds.getConnection();
        return conn;
}
    
    public int modify(String sql, String ary[]){
            int x = 0;
        try(Connection con = this.getConnection();//java7之後，才可寫在try小括號裡，且會自動關閉auto close
                java.sql.PreparedStatement ps = con.prepareStatement(sql);) {
             //Connection con = this.getConnection();
           //  java.sql.PreparedStatement ps = con.prepareStatement(sql); //寫在這要關閉的話需加上Finally區塊並寫上ps.close();
             for(int i=0; i<ary.length;i++)
                ps.setString(i+1, ary[i]);
             x=ps.executeUpdate();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
            
            return x;
        }
    public ArrayList query(String sql, String ary[]){
    
        ArrayList list = new ArrayList();
         try(Connection con = this.getConnection();//java7之後，才可寫在try小括號裡，且會自動關閉auto close
                java.sql.PreparedStatement ps = con.prepareStatement(sql);) {
             //Connection con = this.getConnection();
           //  java.sql.PreparedStatement ps = con.prepareStatement(sql); //寫在這要關閉的話需加上Finally區塊並寫上ps.close();
             for(int i=0; i<ary.length;i++){
                     ps.setString(i+1, ary[i]);
             }
             ResultSet rs = ps.executeQuery();
             java.sql.ResultSetMetaData meta = rs.getMetaData();//可以記錄rs裡面有幾個欄位幾筆資訊
             int cols = meta.getColumnCount();//取得meta裡的欄位並指定給 cols變數
             while(rs.next()){
                    HashMap data = new HashMap();
                    for(int i = 1; i<=cols;i++)
                        data.put(meta.getColumnName(i), rs.getString(i));//前面為取得幾格名稱，後者為取得欄位裡的資訊                    
                     list.add(data);
             }
             rs.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return list;
    }

}